Data Types¶

INT/INTEGER¶

The INTEGER data type is used to represent integers. The value range is available is from -2,147,483,648 to +2,147,483,647. SMALLINT is used for small integers, and BIGINT is used for big integers.

• If a real number is entered for an INT type, the number is rounded to zero decimal place and the integer value is stored.
• INTEGER and INT are used interchangeably.
• DEFAULT constraint can be specified in a column of this type.

If you specify 8934 as INTEGER, 8934 is stored.
If you specify 7823467 as INTEGER, 7823467 is stored.
If you specify 89.8 to an INTEGER, 90 is stored (all digits after the decimal point are rounded).
If you specify 3458901122 as INTEGER, an error occurs (if the allowable limit is exceeded).

SHORT/SMALLINT¶

The SMALLINT data type is used to represent a small integer type. The value range is available is from -32,768 to +32,767.

• If a real number is entered for an SMALLINT type, the number is rounded to zero decimal place and the integer value is stored.
• SMALLINT and SHORT are used interchangeably.
• DEFAULT constraint can be specified in a column of this type.

If you specify 8934 as SMALLINT, 8934 is stored.
If you specify 34.5 as SMALLINT, 35 is stored (all digits after the decimal point are rounded).
If you specify 23467 as SMALLINT, 23467 is stored.
If you specify 89354 as SMALLINT, an error occurs (if the allowable limit is exceeded).

BIGINT¶

The BIGINT data type is used to represent big integers. The value range is available from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

• If a real number is entered for a BIG type, the number is rounded to zero decimal place and the integer value is stored.

• Based on the precision and the range of representation, the following order is applied.

  SMALLINT ??**INTEGER** ??**BIGINT** ??**NUMERIC**

• DEFAULT constraint can be specified in a column of this type.

If you specify 8934 as BIGINT, 8934 is stored.
If you specify 89.1 as BIGINT, 89 is stored.
If you specify 89.8 as BIGINT, 90 is stored (all digits after the decimal point are rounded).
If you specify 3458901122 as BIGINT, 3458901122 is stored.

NUMERIC/DECIMAL¶

NUMERIC or DECIMAL data types are used to represent fixed-point numbers. As an option, the total number of digits (precision) and the number of digits after the decimal point (scale) can be specified for definition. The minimum value for the precision p is 1. When the precision p is omitted, you cannot enter data whose integer part exceeds 15 digits because the default value is 15. If the scale s is omitted, an integer rounded to the first digit after the decimal point is returned because the default value is 0.

NUMERIC [(p[, s])]

• Precision must be equal to or greater than scale.
• Precision must be equal to or greater than the number of integer digits + scale.
• NUMERIC, DECIMAL, and DEC are used interchangeably.
• To check how the precision and the scale became changed when you operate with NUMERIC typed values, see Arithmetic Operations and Type Casting of Numeric Data Types.
• DEFAULT constraint can be specified in a column of this type.

If you specify 12345.6789 as NUMERIC, 12346 is stored (it rounds to the first place after the decimal point since 0 is the default value of scale).
If you specify 12345.6789 as NUMERIC(4), an error occurs (precision must be equal to or greater than the number of integer digits).
If you declare NUMERIC(3,4), an error occurs (precision must be equal to or greater than the scale).
If you specify 0.12345678 as NUMERIC(4,4), .1235 is stored (it rounds to the fifth place after the decimal point).
If you specify -0.123456789 as NUMERIC(4,4), -.1235 is stored (it rounds to the fifth place after decimal point and then prefixes a minus (-) sign).

FLOAT/REAL¶

The FLOAT (or REAL) data type represents floating point numbers.

The ranges of values that can be described as normalized values are from -3.402823466E+38 to -1.175494351E-38, 0, and from +1.175494351E-38 to +3.402823466E+38, whereas the values other than normalized values, which are closer to 0, are described as de-normalized values. It conforms to the ANSI/IEEE 754-1985 standard.

The minimum value for the precision p is 1 and the maximum value is 38. When the precision p is omitted or it is specified as seven or less, it is represented as single precision (in 7 significant figures). If the precision p is greater than 7 and equal to or less than 38, it is represented as double precision (in 15 significant figures) and it is converted into DOUBLE data type.

FLOAT data types must not be used if you want to store a precise value that exceeds the number of significant figures, as they only store the approximate value of any input value over 7 significant figures.

FLOAT[(p)]

• FLOAT is in 7 significant figures.
• Extra cautions are required when comparing data because the FLOAT type stores approximate numeric.
• FLOAT and REAL are used interchangeably.
• DEFAULT constraint can be specified in a column of this type.

If you specify 16777217 as FLOAT, 16777216 is stored and 1.677722e+07 is displayed (if precision is omitted, 8-th digit is rounded up because it is represented as 7 significant figures).
If you specify 16777217 as FLOAT(5), 16777216 is stored and 1.677722e+07 is displayed (if precision is in seven or less, 8-th digit is rounded up because it is represented as 7 significant figures).
If you specify 16777.217 as FLOAT(5), 16777.216 is stored and 1.677722e+04 is displayed (if precision is in seven or less, 8-th digit is rounded up because it is represented as 7 significant figures).
If you specify 16777.217 as FLOAT(10), 16777.217 is stored and 1.677721700000000e+04 is displayed (if precision is greater than 7 and less than or equal to 38, zeroes are added because it is represented as 15 significant figures).

DOUBLE/DOUBLE PRECISION¶

The DOUBLE data type is used to represent floating point numbers.

The ranges of values that can be described as normalized values are from -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and from 2.2250738585072014E-308 to 1.7976931348623157E+308, whereas the values other than normalized values, which are closer to 0, are described as de-normalized values. It conforms to the ANSI/IEEE 754-1985 standard.

The precision p is not specified. The data specified as this data type is represented as double precision (in 15 significant figures).

DOUBLE data types must not be used if you want to store a precise value that exceeds the number of significant figures, as they only store the approximate value of any input value over 15 significant figures.

• DOUBLE is in 15 significant figures.
• Extra caution is required when comparing data because the DOUBLE type stores approximate numeric.
• DOUBLE and DOUBLE PRECISION are used interchangeably.
• DEFAULT constraint can be specified in a column of this type.

If you specify 1234.56789 as DOUBLE, 1234.56789 is stored and 1.234567890000000e+03 is displayed.
If you specify 9007199254740993 as DOUBLE, 9007199254740992 is stored and 9.007199254740992e+15 is displayed.

DATE¶

The DATE data type is used to represent the year (yyyy), month (mm) and day (dd). Supported range is "01/01/0001" to "12/31/9999." The year can be omitted. If it is, the year value of the current system is specified automatically. The specified input/output types are as follows:

date'mm/dd[/yyyy]'
date'[yyyy-]mm-dd'

• All fields must be entered as integer.
• The date value is displayed in the type of 'MM/DD/YYYY' in CSQL, and it is displayed in the type of 'YYYY-MM-DD' in JDBC application programs and the CUBRID Manager.
• The TO_DATE() function is used to convert a character string type into a DATE type.
• 0 is not allowed to input in year, month, and day; however, '0000-00-00', which every digit consisting of year, month, and day is 0, is allowed as an exception.
• DEFAULT constraint can be specified in a column of this type.

DATE'2008-10-31' is displayed as '10/31/2008'.
DATE'10/31' is displayed as '10/31/2011'(if a value for year is omitted, the current year is automatically specified).
DATE'00-10-31' is displayed as '10/31/2000'.
DATE'0000-10-31' is displayed as an error (a year value should be at least 1).
DATE'70-10-31' is displayed as '10/31/1970'.
DATE'0070-10-31' is displayed as '10/31/0070'.

TIME¶

The TIME data type is used to represent the hour (hh), minute (mm) and second (ss). Supported range is "00:00:00" to "23:59:59." Second can be omitted; if it is, 0 seconds is specified. Both 12-hour and 24-hour notations are allowed as an input format. The input format of TIME is as follows:

time'hh:mi[:ss] [am | pm]'

• All items must be entered as integer.
• AM/PM time notation is used to display time in the CSQL; while the 24-hour notation is used in the CUBRID Manager.
• AM/PM can be specified in the 24-hour notation. An error occurs if the time specified does not follow the AM/PM format.
• Every time value is stored in the 24-hour notation.
• The TO_TIME() function is used to return a character string type into a TIME type.
• DEFAULT constraint can be specified in a column of this type.

TIME'00:00:00' is outputted as '12:00:00 AM'.
TIME'1:15' is regarded as '01:15:00 AM'.
TIME'13:15:45' is regarded as '01:15:45 PM'.
TIME'13:15:45 pm' is stored normally.
TIME'13:15:45 am' is an error (an input value does not match the AM/PM format).

TIMESTAMP¶

The TIMESTAMP data type is used to represent a data value in which the date (year, month, date) and time (hour, minute, second) are combined. The range of representable value is between GMT '1970-01-01 00:00:01' and '2038-01-19 03:14:07'. The DATETIME type can be used if the value is out of range or data in milliseconds is stored. The input format of TIMESTAMP is as follows:

timestamp'hh:mi[:ss] [am|pm] mm/dd[/yyyy]'
timestamp'hh:mi[:ss] [am|pm] [yyyy-]mm-dd'

timestamp'mm/dd[/yyyy] hh:mi[:ss] [am|pm]'
timestamp'[yyyy-]mm-dd hh:mi[:ss] [am|pm]'

• All fields must be entered in integer format.
• If the year is omitted, the current year is specified by default. If the time value (hour/minute/second) is omitted, 12:00:00 AM is specified.
• You can store the timestamp value of the system in the TIMESTAMP type by using the SYS_TIMESTAMP(or SYSTIMESTAMP, CURRENT_TIMESTAMP).
• The TIMESTAMP() or TO_TIMESTAMP() function is used to cast a character string type into a TIMESTAMP type.
• 0 is not allowed to input in year, month, and day; however, '0000-00-00 00:00:00', which every digit consisting of year, month, day, hour, minute, and second is 0, is allowed as an exception. GMT timestamp'1970-01-01 12:00:00 AM' or KST timestamp'1970-01-01 09:00:00 AM' is translated into timestamp'0000-00-00 00:00:00'.
• DEFAULT constraint can be specified in a column of this type.

TIMESTAMP'10/31' is outputted as '12:00:00 AM 10/31/2011' (if the value for year/time is omitted, a default value is outputted ).
TIMESTAMP'10/31/2008' is outputted as '12:00:00 AM 10/31/2008' (if the value for time is omitted, a default value is outputted ).
TIMESTAMP'13:15:45 10/31/2008' is outputted as '01:15:45 PM 10/31/2008'.
TIMESTAMP'01:15:45 PM 2008-10-31' is outputted as '01:15:45 PM 10/31/2008'.
TIMESTAMP'13:15:45 2008-10-31' is outputted as '01:15:45 PM 10/31/2008'.
TIMESTAMP'10/31/2008 01:15:45 PM' is outputted as '01:15:45 PM 10/31/2008'.
TIMESTAMP'10/31/2008 13:15:45' is outputted as '01:15:45 PM 10/31/2008'.
TIMESTAMP'2008-10-31 01:15:45 PM' is outputted as '01:15:45 PM 10/31/2008'.
TIMESTAMP'2008-10-31 13:15:45' is outputted as '01:15:45 PM 10/31/2008'.
An error occurs on TIMESTAMP '2099-10-31 01:15:45 PM' (out of range to represent TIMESTAMP).

DATETIME¶

The DATETIME data type is used to represent a data value in which the data (year, month, date) and time (hour, minute, second) are combined. The range of representable value is between 0001-01-01 00:00:00.000 and 9999-12-31 23:59:59.999 (GMT). The input format of TIMESTAMP is as follows:

datetime'hh:mi[:ss[.msec]] [am|pm] mm/dd[/yyyy]'
datetime'hh:mi[:ss[.msec]] [am|pm] [yyyy-]mm-dd'
datetime'mm/dd[/yyyy] hh:mi[:ss[.ff]] [am|pm]'
datetime'[yyyy-]mm-dd hh:mi[:ss[.ff]] [am|pm]'

• All fields must be entered as integer.
• If you year is omitted, the current year is specified by default. If the value (hour, minute/second) is omitted, 12:00:00.000 AM is specified.
• You can store the timestamp value of the system in the DATETIME type by using the SYS_DATETIME (or SYSDATETIME, CURRENT_DATETIME, CURRENT_DATETIME(), NOW()) function.
• The TO_DATETIME() function is used to convert a string type into a DATETIME type.
• 0 is not allowed to input in year, month, and day; however, '0000-00-00 00:00:00', which every digit consisting of year, month, day, hour, minute, and second is 0, is allowed as an exception.
• DEFAULT constraint can be specified in a column of this type.

DATETIME'10/31' is outputted as '12:00:00.000 AM 10/31/2011' (if the value for year/time is omitted, a default value is outputted).
DATETIME'10/31/2008' is outputted as '12:00:00.000 AM 10/31/2008'.
DATETIME'13:15:45 10/31/2008' is outputted as '01:15:45.000 PM 10/31/2008'.
DATETIME'01:15:45 PM 2008-10-31' is outputted as '01:15:45.000 PM 10/31/2008'.
DATETIME'13:15:45 2008-10-31' is outputted as '01:15:45.000 PM 10/31/2008'.
DATETIME'10/31/2008 01:15:45 PM' is outputted as '01:15:45.000 PM 10/31/2008'.
DATETIME'10/31/2008 13:15:45' is outputted as '01:15:45.000 PM 10/31/2008'.
DATETIME'2008-10-31 01:15:45 PM' is outputted as '01:15:45.000 PM 10/31/2008'.
DATETIME'2008-10-31 13:15:45' is outputted as '01:15:45.000 PM 10/31/2008'.
DATETIME'2099-10-31 01:15:45 PM' is outputted as '01:15:45.000 PM 10/31/2099'.

CASTing a String to Date/Time Type¶

Timezone Configuration¶

The below shows the timezone related parameters configured in cubrid.conf. For parameter's configuration, see cubrid.conf Configuration File and Default Parameters.

• timezone

  Specifies a timezone for a session. The default is a value of server_timezone.

• server_timezone

  Specifies a timezone for a server. The default is a timezone of OS.

• tz_leap_second_support

  Sets for support for leap second as yes or no. The default is no.

Timezone Function¶

The following are timezone related functions. For each function's detail usage, click each function's name.

• DBTIMEZONE()
• SESSIONTIMEZONE()
• FROM_TZ()
• NEW_TIME()
• TZ_OFFSET()

Functions with a Timezone Type¶

All functions which use DATETIME, TIMESTAMP or TIME typed value in their input value, can use timezone typed value.

The below is an example of using timezone typed values, it works the same as the case without timezone. Exceptionally, if the type name ends with LTZ, the output value of this type follows the local timezone's setting (timezone parameter).

On the below example, the default unit of a number is millisecond, which is the minimum unit of DATETIME type.

SELECT datetimeltz '09/01/2009 03:30:30 pm' + 1;

03:30:30.001 PM 09/01/2009 Asia/Seoul

SELECT datetimeltz '09/01/2009 03:30:30 pm' - 1;

03:30:29.999 PM 09/01/2009 Asia/Seoul

On the below example, the default unit of a number is second, which is the minimum unit of TIMESTAMP type.

SELECT timestamptz '09/01/2009 03:30:30 pm' + 1;

03:30:31 PM 09/01/2009 Asia/Seoul

SELECT timestamptz '09/01/2009 03:30:30 pm' - 1;

03:30:29 PM 09/01/2009 Asia/Seoul

SELECT EXTRACT (hour from datetimetz'10/15/1986 5:45:15.135 am Europe/Bucharest');

5

A type which the name ends with LTZ follows the setting of local timezone. Therefore, if the value of timezone parameter is set to 'Asia/Seoul', EXTRACT function returns hour value of this timezone.

-- csql> ;set timezone='Asia/Seoul'

SELECT EXTRACT (hour from datetimeltz'10/15/1986 5:45:15.135 am Europe/Bucharest');

12

Conversion Functions for Timezone Types¶

The following are functions converting a string to a date/time typed value, or date/time typed value to a string; The value can include an information like an offset, a zone and a daylight saving.

• DATE_FORMAT()
• STR_TO_DATE()
• TO_CHAR()
• TO_DATETIME_TZ()
• TO_TIMESTAMP_TZ()

For each function's usage, see the each function's explanation by clicking the function name.

SELECT DATE_FORMAT (datetimetz'2012-02-02 10:10:10 Europe/Zurich CET', '%TZR %TZD %TZH %TZM');
SELECT STR_TO_DATE ('2001-10-11 02:03:04 AM Europe/Bucharest EEST', '%Y-%m-%d %h:%i:%s %p %TZR %TZD');
SELECT TO_CHAR (datetimetz'2001-10-11 02:03:04 AM Europe/Bucharest EEST');
SELECT TO_DATETIME_TZ ('2001-10-11 02:03:04 AM Europe/Bucharest EEST');
SELECT TO_TIMESTAMP_TZ ('2001-10-11 02:03:04 AM Europe/Bucharest');

CUBRID uses the region name of timezone in the IANA(Internet Assigned Numbers Authority) timezone database region; for IANA timezone, see http://www.iana.org/time-zones.

IANA Timezone¶

In IANA(Internet Assigned Numbers Authority) timezone database, there are lots of codes and data which represent the history of localtime for many representative locations around the globe.

This database is periodically updated to reflect changes made by political bodies to time zone boundaries, UTC offsets, and daylight-saving rules. Its management procedure is described in BCP 175: Procedures for Maintaining the Time Zone Database. For more details, see http://www.iana.org/time-zones.

CUBRID supports IANA timezone, and a user can use the IANA timezone library in the CUBRID installation package as it is. If you want to update as the recent timezone, update timezone first, compile timezone library, and restart the database.

Regarding this, see Compiling Timezone Library.

BIT(n)¶

Fixed-length binary or hexadecimal bit strings are represented as BIT (n), where n is the maximum number of bits. If n is not specified, the length is set to 1. If n is not specified, the length is set to 1. The bit string is filled with 8-bit unit from the left side. For example, the value of B'1' is the same as the value of B'10000000'. Therefore, it is recommended to declare a length by 8-bit unit, and input a value by 8-bit unit.

• n must be a number greater than 0.
• If the length of the string exceeds n, it is truncated and filled with 0s.
• If a bit string smaller than n is stored, the remainder of the string is filled with 0s.
• DEFAULT constraint can be specified in a column of this type.

CREATE TABLE bit_tbl(a1 BIT, a2 BIT(1), a3 BIT(8), a4 BIT VARYING);
INSERT INTO bit_tbl VALUES (B'1', B'1', B'1', B'1');
INSERT INTO bit_tbl VALUES (0b1, 0b1, 0b1, 0b1);
INSERT INTO bit_tbl(a3,a4) VALUES (B'1010', B'1010');
INSERT INTO bit_tbl(a3,a4) VALUES (0xaa, 0xaa);
SELECT * FROM bit_tbl;

  a1                    a2                    a3                    a4

=========================================================================
  X'8'                  X'8'                  X'80'                 X'8'
  X'8'                  X'8'                  X'80'                 X'8'
  NULL                  NULL                  X'a0'                 X'a'
  NULL                  NULL                  X'aa'                 X'aa'

BIT VARYING(n)¶

A variable-length bit string is represented as BIT VARYING (n), where n is the maximum number of bits. If n is not specified, the length is set to 1,073,741,823 (maximum value). n is the maximum number of bits. If n is not specified, the maximum length is set to 1,073,741,823. The bit string is filled with 8-bit values from the left side. For example, the value of B'1' is the same as the value of B'10000000'. Therefore, it is recommended to declare a length by 8-bit unit, and input a value by 8-bit unit.

• If the length of the string exceeds n, it is truncated and filled with 0s.
• The remainder of the string is not filled with 0s even if a bit string smaller than n is stored.
• n must be a number greater than 0.
• DEFAULT constraint can be specified in a column of this type.

CREATE TABLE bitvar_tbl(a1 BIT VARYING, a2 BIT VARYING(8));
INSERT INTO bitvar_tbl VALUES (B'1', B'1');
INSERT INTO bitvar_tbl VALUES (0b1010, 0b1010);
INSERT INTO bitvar_tbl VALUES (0xaa, 0xaa);
INSERT INTO bitvar_tbl(a1) VALUES (0xaaa);
SELECT * FROM bitvar_tbl;

  a1                    a2
============================================
  X'8'                  X'8'
  X'a'                  X'a'
  X'aa'                 X'aa'
  X'aaa'                NULL

INSERT INTO bitvar_tbl(a2) VALUES (0xaaa);

ERROR: Data overflow coercing X'aaa' to type bit varying.

CHAR(n)¶

A fixed-length character string is represented as CHAR (n), in which n represents the number of characters. If n is not specified, the value is specified as 1, default value.

When the length of a character string exceeds n, they are truncated. When character string which is shorter than n is stored, whitespace characters are used to fill up the trailing space.

CHAR (n) and CHARACTER (n) are used interchangeably.

• n is an integer between 1 and 1,073,741,823 (1G).
• Empty quotes (' ') are used to represent a blank string. In this case, the return value of the LENGTH function is not 0, but is the fixed length defined in CHAR (n). That is, if you enter a blank string into a column with CHAR (10), the LENGTH is 10; if you enter a blank value into a CHAR with no length specified, the LENGTH is the default value 1.
• Space characters used as filling characters are considered to be smaller than any other characters, including special characters.

If you specify 'pacesetter' as CHAR(12), 'pacesetter ' is stored (a 10-character string plus two whitespace characters).
If you specify 'pacesetter ' as CHAR(10), 'pacesetter' is stored (a 10-character string; two whitespace characters are truncated).
If you specify 'pacesetter' as CHAR(4), 'pace' is stored (truncated as the length of the character string is greater than 4).
If you specify 'p ' as CHAR, 'p' is stored (if n is not specified, the length is set to the default value 1).

• DEFAULT constraint can be specified in a column of this type.

VARCHAR(n)/CHAR VARYING(n)¶

Variable-length character strings are represented as VARCHAR (n), where n represents the number of characters. If n is not specified, the value is specified as 1,073,741,823, the maximum length.

When the length of a character string exceeds n, they are truncated. When character string which is shorter than n is stored, whitespace characters are used to fill up the trailing space for VARCHAR (n), the length of string used are stored.

VARCHAR (n), CHARACTER, VARYING (n), and CHAR VARYING (n) are used interchangeably.

• STRING is the same as the VARCHAR (maximum length).
• n is an integer between 1 and 1,073,741,823 (1G).
• Empty quotes (' ') are used to represent a blank string. In this case, the return value of the LENGTH function is not 0.

If you specify 'pacesetter' as CHAR(4), 'pace' is stored (truncated as the length of the character string is greater than 4).
If you specify 'pacesetter' as VARCHAR(12), 'pacesetter' is stored (a 10-character string).
If you specify 'pacesetter ' as VARCHAR(12), 'pacesetter ' is stored (a 10-character string plus two whitespace characters).
If you specify 'pacesetter ' as VARCHAR(10), 'pacesetter' is stored (a 10-character string; two whitespace characters are truncated).
If you specify 'p ' as VARCHAR, 'p' is stored (if n is not specified, the default value 1,073,741,823 is used, and the trailing space is not filled with whitespace characters).

• DEFAULT constraint can be specified in a column of this type.

STRING¶

STRING is a variable-length character string data type. STRING is the same as the VARCHAR with the length specified as the maximum value. That is, STRING and VARCHAR (1,073,741,823) have the same value.

Escape Special Characters¶

CUBRID supports two kinds of methods to escape special characters. One is using quotes and the other is using backslash (\).

• Escape with Quotes

  If you set no for the system parameter ansi_quotes in the cubrid.conf file, you can use both double quotes (") and singe quotes (') to wrap strings. The default value for the ansi_quotes parameter is yes, and you can use only single quotes to wrap the string.

  • You should use two single quotes ('') for the single quotes included in the strings wrapped in single quotes.
  • You should use two double quotes ("") for the double quotes included in the strings wrapped in double quotes. (when ansi_quotes = no)
  • You don't need to escape the single quotes included in the string wrapped in double quotes. (when ansi_quotes = no)
  • You don't need to escape the double quotes included in the string wrapped in single quotes.

• Escape with Backslash

  You can use escape using backslash (\) only if you set no for the system parameter no_backslash_escapes in the cubrid.conf file. The default value for the no_backslash_escapes parameter is yes. If the value of no_backslash_escapes is no, the following are the special characters.

  • \' : Single quotes (')
  • \" : Double quotes (")
  • \n : Newline, linefeed character
  • \r : Carriage return character
  • \t : Tab character
  • \\ : Backslash
  • \% : Percent sign (%). For details, see the following description.
  • \_ : Underbar (_). For details, see the following description.

  For all other escapes, the backslash will be ignored. For example, "x" is the same as entering only "x".

  \% and \_ are used in the pattern matching syntax such as LIKE to search percent signs and underbars and are used as a wildcard character if there is no backslash. Outside of the pattern matching syntax, "\%"and "\_" are recognized as normal strings not wildcard characters. For details, see LIKE.

The following is the result of executing Escape if a value for the system parameter ansi_quotes in the cubrid.conf file is yes(default), and a value for no_backslash_escapes is no.

-- ansi_quotes=yes, no_backslash_escapes=no
SELECT STRCMP('single quotes test('')', 'single quotes test(\')');

If you run the above query, backslash is regarded as an escape character. Therefore, above two strings are the same.

   strcmp('single quotes test('')', 'single quotes test('')')
=============================================================
                                                            0

SELECT STRCMP('\a\b\c\d\e\f\g\h\i\j\k\l\m\n\o\p\q\r\s\t\u\v\w\x\y\z', 'a\bcdefghijklm\nopq\rs\tuvwxyz');

If you run the above query, backslash is regarded as an escape character. Therefore, above two strings are the same.

   strcmp('abcdefghijklm
s       uvwxyz', 'abcdefghijklm
s       uvwxyz')
=====================================================================
                                                                    0

SELECT LENGTH('\\');

If you run the above query, backslash is regarded as an escape character. Therefore, the length of above string is 1.

   char_length('\')
===================
                  1

The following is the result of executing Escape if a value for the system parameter ansi_quotes in the cubrid.conf file is yes(default), and a value for no_backslash_escapes is yes(default). Backslash character is regarded as a general character.

-- ansi_quotes=yes, no_backslash_escapes=yes

SELECT STRCMP('single quotes test('')', 'single quotes test(\')');

If you run the above query, the quotation mark is regarded as opened, so the below error occurs. If you input this query on the CSQL interpreter's console, it waits the next quotation mark's input.

ERROR: syntax error, unexpected UNTERMINATED_STRING, expecting SELECT or VALUE or VALUES or '('

SELECT STRCMP('\a\b\c\d\e\f\g\h\i\j\k\l\m\n\o\p\q\r\s\t\u\v\w\x\y\z', 'a\bcdefghijklm\nopq\rs\tuvwxyz');

If you run the above query, backslash is regarded as a general character. Therefore, the result of the comparison between the above two strings shows different.

   strcmp('\a\b\c\d\e\f\g\h\i\j\k\l\m\n\o\p\q\r\s\t\u\v\w\x\y\z', 'a\bcdefghijklm\nopq\rs\tuvwxyz')
===================================================================================================
                                                                                                 -1

SELECT LENGTH('\\');

If you run the above query, backslash is regarded as a general character. Therefore, the length of above string is 2.

   char_length('\\')
====================
                   2

The following shows the result of executing Escape about the LIKE clause when ansi_quotes is yes and no_backslash_escapes is no.

-- ansi_quotes=yes, no_backslash_escapes=no

CREATE TABLE t1 (a VARCHAR(200));
INSERT INTO t1 VALUES ('aaabbb'), ('aaa%');

SELECT a FROM t1 WHERE a LIKE 'aaa\%' ESCAPE '\\';

  a
======================
  'aaa%'

If you run above query, it returns only one row because '%' character is regarded as a general character.

In the string of LIKE clause, backslash is always regarded as a general character. Therefore, if you want to make the '%' character as a general character, not as an pattern matching character, you should specify that '%' is an escape character by using ESCAPE clause. In the ESCAPE clause, backslash is regarded as an escape character. Therefore, we used two backslashes.

If you want use other character than a backslash as an escape character, you can write the query as follows.

SELECT a FROM t1 WHERE a LIKE 'aaa#%' ESCAPE '#';

ENUM Type Comparisons¶

When used in = or IN predicates of the form (<enum_column> <operator> <constant>), CUBRID tries to convert the constant to the ENUM type. If the coercion fails, CUBRID does not return an error but considers the comparison to be false. This is implemented like this in order to allow index scan plans to be generated on these two operators.

For all other comparison operators, the ENUM type is converted to the type of the other operand. If a comparison is performed on two ENUM types, both arguments are converted to CHAR type and the comparison follows CHAR type rules. Except for = and IN, predicates on ENUM columns cannot be used in index scan plans.

To understand these rules, consider the following table:

CREATE TABLE tbl (
    color ENUM ('red', 'yellow', 'blue', 'green')
);

INSERT INTO tbl (color) VALUES (1), (2), (3), (4);

The following query will convert the constant 'red' to the enum value 'red' with index 1

SELECT color FROM tbl WHERE color = 'red';

  color
======================
  'red'

SELECT color FROM tbl WHERE color = 1;

  color
======================
  'red'

The following queries will not return a conversion error but will not return any results:

SELECT color FROM tbl WHERE color = date'2010-01-01';
SELECT color FROM tbl WHERE color = 15;
SELECT color FROM tbl WHERE color = 'asdf';

In the following queries the ENUM type will be converted to the type of the other operand:

-- CHAR comparison using the enum value
SELECT color FROM tbl WHERE color < 'pink';

  color
======================
  'blue'
  'green'

-- INTEGER comparison using the enum index
SELECT color FROM tbl WHERE color > 3;

  color
======================
  'green'

-- Conversion error
SELECT color FROM tbl WHERE color > date'2012-01-01';

ERROR: Cannot coerce value of domain "enum" to domain "date".

ENUM Type Ordering¶

Values of the ENUM type are ordered by value index, not by enum value. When defining a column with ENUM type, users also define the ordering of the enum values.

SELECT color FROM tbl ORDER BY color ASC;

  color
======================
  'red'
  'yellow'
  'blue'
  'green'

To order the values stored in an ENUM type column as CHAR values, users can cast the enum value to the CHAR type:

SELECT color FROM tbl ORDER BY CAST (color AS VARCHAR) ASC;

  color
======================
  'blue'
  'green'
  'red'
  'yellow'

Notes¶

The ENUM type is not a reusable type. If several columns require the same set of values, an ENUM type must be defined for each one. When comparing two columns of ENUM type, the comparison is performed as if the columns were coerced to CHAR type even if the two ENUM types define the same set of values.

Using the ALTER ... CHANGE statement to modify the set of values of an ENUM type is only allowed if the value of the system parameter alter_table_change_type_strict is set to yes. In this case, CUBRID uses enum value (the char-literal) to convert values to the new domain. If a value is outside of the new ENUM type values set, it is automatically mapped to the empty string('').

CREATE TABLE tbl(color ENUM ('red', 'green', 'blue'));
INSERT INTO tbl VALUES('red'), ('green'), ('blue');

The following statement will extend the ENUM type with the value 'yellow':

ALTER TABLE tbl CHANGE color color ENUM ('red', 'green', 'blue', 'yellow');
INSERT into tbl VALUES(4);
SELECT color FROM tbl;

  color
======================
  'red'
  'green'
  'blue'
  'yellow'

The following statement will change all tuples with value 'green' to value 'red' because the value 'green' cannot be converted the new ENUM type:

ALTER TABLE tbl CHANGE color color enum ('red', 'yellow', 'blue');
SELECT color FROM tbl;

  color
======================
  'red'
  ''
  'blue'
  'yellow'

The ENUM type is mapped to char-string types in CUBRID drivers. The following example shows how to use the ENUM type in a JDBC application:

Statement stmt = connection.createStatement("SELECT color FROM tbl");
ResultSet rs = stmt.executeQuery();

while(rs.next()) {
   System.out.println(rs.getString());
}

The following example shows how to use the ENUM type in a CCI application.

req_id = cci_prepare (conn, "SELECT color FROM tbl", 0, &err);
error = cci_execute (req_id, 0, 0, &err);
if (error < CCI_ER_NO_ERROR)
{
    /* handle error */
}

error = cci_cursor (req_id, 1, CCI_CURSOR_CURRENT, &err);
if (error < CCI_ER_NO_ERROR)
{
    /* handle error */
}

error = cci_fetch (req_id, &err);
if (error < CCI_ER_NO_ERROR)
{
    /* handle error */
}

cci_get_data (req, idx, CCI_A_TYPE_STR, &data, 1);

BLOB¶

A type that stores binary data outside the database. The maximum length of BLOB data is the maximum file size creatable in an external storage. In SQL statements, the BLOB type expresses the input and output value in a bit string. That is, it is compatible with the BIT (n) and BIT VARYING (n) types, and only an explicit type change is allowed. If data lengths differ from one another, the maximum length is truncated to fit the smaller one. When converting the BLOB type value to a binary value, the length of the converted data cannot exceed 1GB. When converting binary data to the BLOB type, the size of the converted data cannot exceed the maximum file size provided by the BLOB storage.

CLOB¶

A type that stores character string data outside the database. The maximum length of CLOB data is the maximum file size creatable in an external storage. In SQL statements, the CLOB type expresses the input and output value in a character string. That is, it is compatible with the CHAR (n), VARCHAR (n) types. However, only an explicit type change is allowed, and if data lengths are different from one another, the maximum length is truncated to fit to the smaller one. When converting the CLOB type value to a character string, the length of the converted data cannot exceed 1 GB. When converting a character string to the CLOB type, the size of the converted data cannot exceed the maximum file size provided by the CLOB storage.

To Create and alter LOB¶

BLOB / CLOB type columns can be created/added/deleted by using a CREATE TABLE statement or an ALTER TABLE statement.

• You cannot create the index file for a LOB type column.
• You cannot define the PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL constraints for a LOB type column. However, SHARED property cannot be defined and DEFAULT property can only be defined by the NULL value.
• LOB type column/data cannot be the element of collection type.
• If you are deleting a record containing a LOB type column, all files located inside a LOB column value (Locator) and the external storage will be deleted. When a record containing a LOB type column is deleted in a basic key table, and a record of a foreign key table that refers to the foregoing details is deleted at once, all LOB files located in a LOB column value (Locator) and the external storage will be deleted. However, if the relevant table is deleted by using a DROP TABLE statement, or a LOB column is deleted by using an ALTER TABLE...DROP statement, only a LOB column value (LOB Locator) is deleted, and the LOB files inside the external storage which a LOB column refers to will not be deleted.

-- creating a table and CLOB column
CREATE TABLE doc_t (doc_id VARCHAR(64) PRIMARY KEY, content CLOB);

-- an error occurs when UNIQUE constraint is defined on CLOB column
ALTER TABLE doc_t ADD CONSTRAINT content_unique UNIQUE(content);

-- an error occurs when creating an index on CLOB column
CREATE INDEX i_doc_t_content ON doc_t (content);

-- creating a table and BLOB column
CREATE TABLE image_t (image_id VARCHAR(36) PRIMARY KEY, doc_id VARCHAR(64) NOT NULL, image BLOB);

-- an error occurs when adding a BOLB column with NOT NULL constraint
ALTER TABLE image_t ADD COLUMN thumbnail BLOB NOT NULL;

-- an error occurs when adding a BLOB column with DEFAULT attribute
ALTER TABLE image_t ADD COLUMN thumbnail2 BLOB DEFAULT BIT_TO_BLOB(X'010101');

To store and update LOB¶

In a BLOB / CLOB type column, each BLOB / CLOB type value is stored, and if binary or character string data is input, you must explicitly change the types by using each BIT_TO_BLOB() and CHAR_TO_CLOB() function.

If a value is input in a LOB column by using an INSERT statement, a file is created in an external storage internally and the relevant data is stored; the relevant file path (Locator) is stored in an actual column value.

If a record containing a LOB column uses a DELETE statement, a file to which the relevant LOB column refers will be deleted simultaneously.

If a LOB column value is changed using an UPDATE statement, the column value will be changed following the operation below, according to whether a new value is NULL or not.

• If a LOB type column value is changed to a value that is not NULL : If a Locator that refers to an external file is already available in a LOB column, the relevant file will be deleted. A new file is created afterwards. After storing a value that is not NULL, a Locator for a new file will be stored in a LOB column value.
• If changing a LOB type column value to NULL : If a Locator that refers to an external file is already available in a LOB column, the relevant file will be deleted. And then NULL is stored in a LOB column value.

-- inserting data after explicit type conversion into CLOB type column
INSERT INTO doc_t (doc_id, content) VALUES ('doc-1', CHAR_TO_CLOB('This is a Dog'));
INSERT INTO doc_t (doc_id, content) VALUES ('doc-2', CHAR_TO_CLOB('This is a Cat'));

-- inserting data after explicit type conversion into BLOB type column
INSERT INTO image_t VALUES ('image-0', 'doc-0', BIT_TO_BLOB(X'000001'));
INSERT INTO image_t VALUES ('image-1', 'doc-1', BIT_TO_BLOB(X'000010'));
INSERT INTO image_t VALUES ('image-2', 'doc-2', BIT_TO_BLOB(X'000100'));

-- inserting data from a sub-query result
INSERT INTO image_t SELECT 'image-1010', 'doc-1010', image FROM image_t WHERE image_id = 'image-0';

-- updating CLOB column value to NULL
UPDATE doc_t SET content = NULL WHERE doc_id = 'doc-1';

-- updating CLOB column value
UPDATE doc_t SET content = CHAR_TO_CLOB('This is a Dog') WHERE doc_id = 'doc-1';

-- updating BLOB column value
UPDATE image_t SET image = (SELECT image FROM image_t WHERE image_id = 'image-0') WHERE image_id = 'image-1';

-- deleting BLOB column value and its referencing files
DELETE FROM image_t WHERE image_id = 'image-1010';

To access LOB¶

When you get a LOB type column, the data stored in a file to which the column refers will be displayed. You can execute an explicit type change by using CAST() operator, CLOB_TO_CHAR() and BLOB_TO_BIT() function.

• If the query is executed in CSQL, a column value (Locator) will be displayed, instead of the data stored in a file. To display the data to which a BLOB / CLOB column refers, it must be changed to strings by CLOB_TO_CHAR() function.
• To use the string process function, the strings need to be converted by CLOB_TO_CHAR() function.
• You cannot specify a LOB column in ** GROUP BY** clause and ORDER BY clause.
• Comparison operators, relational operators, IN, NOT IN operators cannot be used to compare LOB columns. However, IS NULL expression can be used to compare whether it is a LOB column value (Locator) or NULL. This means that TRUE will be returned when a column value is NULL, and if a column value is NULL, there is no file to store LOB data.
• When a LOB column is created, and the file is deleted after data input, a LOB column value (Locator) will become a state that is referring to an invalid file. As such, using CLOB_TO_CHAR(), BLOB_TO_BIT(), CLOB_LENGTH() and BLOB_LENGTH() functions on the columns that have mismatching LOB Locator and a LOB data file enables them to display NULL.

-- displaying locator value when selecting CLOB and BLOB column in CSQL interpreter
SELECT doc_t.doc_id, content, image FROM doc_t, image_t WHERE doc_t.doc_id = image_t.doc_id;

  doc_id                content               image
==================================================================
  'doc-1'               file:/home1/data1/ces_658/doc_t.00001282208855807171_7329  file:/home1/data1/ces_318/image_t.00001282208855809474_7474
  'doc-2'               file:/home1/data1/ces_180/doc_t.00001282208854194135_5598  file:/home1/data1/ces_519/image_t.00001282208854205773_1215

2 rows selected.

-- using string functions after coercing its type by CLOB_TO_CHAR( )
SELECT CLOB_TO_CHAR(content), SUBSTRING(CLOB_TO_CHAR(content), 10) FROM doc_t;

   clob_to_char(content)  substring( clob_to_char(content) from 10)
============================================
  'This is a Dog'       ' Dog'
  'This is a Cat'       ' Cat'

2 rows selected.

SELECT CLOB_TO_CHAR(content) FROM doc_t WHERE CLOB_TO_CHAR(content) LIKE '%Dog%';

   clob_to_char(content)
======================
  'This is a Dog'

SELECT CLOB_TO_CHAR(content) FROM doc_t ORDER BY CLOB_TO_CHAR(content);

   clob_to_char(content)
======================
  'This is a Cat'
  'This is a Dog'

SELECT * FROM doc_t WHERE content LIKE 'This%';

  doc_id                content
============================================
  'doc-1'               file:/home1/data1/ces_004/doc_t.00001366272829040346_0773
  'doc-2'               file:/home1/data1/ces_256/doc_t.00001366272815153996_1229

-- an error occurs when LOB column specified in ORDER BY/GROUP BY clauses
SELECT * FROM doc_t ORDER BY content;

ERROR: doc_t.content can not be an ORDER BY column

Functions and Operators for LOB¶

You can explicitly cast bit/string type to BLOB/CLOB type and BLOB/CLOB type to bit/string type with CAST() operator. For more details, see CAST() operator.

CAST (<bit_type_column_or_value> AS { BLOB | CLOB })
CAST (<char_type_column_or_value> AS { BLOB | CLOB })

These are the functions for BLOB/CLOB types. For more details, refer LOB Functions.

• CLOB_TO_CHAR()
• BLOB_TO_BIT()
• CHAR_TO_CLOB()
• BIT_TO_BLOB()
• CHAR_TO_BLOB()
• CLOB_FROM_FILE()
• BLOB_FROM_FILE()
• CLOB_LENGTH()
• BLOB_LENGTH()

To create and manage LOB storage¶

By default, the LOB data file is stored in the <db-volume-path>/lob directory where database volume is created. However, if the lob base path is specified with createdb -B option when creating the database, LOB data files will be stored in the directory designated. However, if the specified directory does not exist, CUBRID tries to create the directory and display an error message when it fails to create it. For more details, see createdb -B option.

# image_db volume is created in the current work directory, and a LOB data file will be stored.
% cubrid createdb image_db en_US

# LOB data file is stored in the "/home1/data1" path within a local file system.
% cubrid createdb --lob-base-path="file:/home1/data1" image_db en_US

You can identify a directory where a LOB file will be stored by executing the cubrid spacedb utility.

% cubrid spacedb image_db

Space description for database 'image_db' with pagesize 16.0K. (log pagesize: 16.0K)

Volid  Purpose  total_size  free_size  Vol Name

    0  GENERIC      512.0M     510.1M  /home1/data1/image_db

Space description for temporary volumes for database 'image_db' with pagesize 16.0K.

Volid  Purpose  total_size  free_size  Vol Name

LOB space description file:/home1/data1

To expand or change the lob-base-path of the database, change its lob-base-path of databases.txt file. Restart the database server to apply the changes made to databases.txt. However, even if you change the lob-base-path of databases.txt, access to the LOB data stored in a previous storage is possible.

# You can change to a new directory from the lob-base-path of databases.txt file.
% cat $CUBRID_DATABASES/databases.txt

#db-name     vol-path           db-host       log-path              lob-base-path
image_db     /home1/data1       localhost     /home1/data1          file:/home1/data2

Backup/recovery for data files of LOB type columns are not supported, while those for meta data(Locator) are supported.

If you are copying a database by using copydb utility, you must configure the databases.txt additionally, as the LOB file directory path will not be copied if the related option is not specified. For more details, see the copydb -B and copydb --copy-lob-path options.

Transaction and Recovery¶

Commit/Rollback for LOB data changes are supported. That is, it ensures the validation of mapping between LOB Locator and actual LOB data within transactions, and it supports recovery during DB errors. This means that an error will be displayed in case of mapping errors between LOB Locator and LOB data due to the rollback of the relevant transactions, as the database is terminated during transactions. See the example below.

-- csql> ;AUTOCOMMIT OFF

CREATE TABLE doc_t (doc_id VARCHAR(64) PRIMARY KEY, content CLOB);
INSERT INTO doc_t VALUES ('doc-10', CHAR_TO_CLOB('This is content'));
COMMIT;
UPDATE doc_t SET content = CHAR_TO_CLOB('This is content 2') WHERE doc_id = 'doc-10';
ROLLBACK;
SELECT doc_id, CLOB_TO_CHAR(content) FROM doc_t WHERE doc_id = 'doc-10';

  doc_id   content
=========================================================
  'doc-10'  'This is content'

-- csql> ;AUTOCOMMIT OFF

INSERT INTO doc_t VALUES ('doc-11', CHAR_TO_CLOB ('This is content'));
COMMIT;
UPDATE doc_t SET content = CHAR_TO_CLOB('This is content 3') WHERE doc_id = 'doc-11';

-- system crash occurred and then restart server
SELECT doc_id, CLOB_TO_CHAR(content) FROM doc_t WHERE doc_id = 'doc-11';

-- Error : LOB Locator references to the previous LOB data because only LOB Locator is rollbacked.

SET¶

SET is a collection type in which each element has different values. Elements of a SET are allowed to have only one data type. It can have records of other tables.

CREATE TABLE set_tbl (col_1 SET (CHAR(1)));
INSERT INTO set_tbl VALUES ({'c','c','c','b','b','a'});
INSERT INTO set_tbl VALUES ({NULL});
INSERT INTO set_tbl VALUES ({''});
SELECT * FROM set_tbl;

  col_1
======================
{'a', 'b', 'c'}
{NULL}
{' '}

SELECT CAST (col_1 AS MULTISET), CAST (col_1 AS LIST) FROM set_tbl;

   cast(col_1 as multiset)   cast(col_1 as sequence)
============================================
  {'a', 'b', 'c'}  {'a', 'b', 'c'}
  {NULL}  {NULL}
  {' '}  {' '}

INSERT INTO set_tbl VALUES ('');

ERROR: Casting '' to type set is not supported.

MULTISET¶

MULTISET is a collection type in which duplicated elements are allowed. Elements of a MULTISET are allowed to have only one data type. It can have records of other tables.

CREATE TABLE multiset_tbl (col_1 MULTISET (CHAR(1)));
INSERT INTO multiset_tbl VALUES ({'c','c','c','b','b', 'a'});
SELECT * FROM multiset_tbl;

  col_1
======================
  {'a', 'b', 'b', 'c', 'c', 'c'}

SELECT CAST(col_1 AS SET), CAST(col_1 AS LIST) FROM multiset_tbl;

   cast(col_1 as set)   cast(col_1 as sequence)
============================================
  {'a', 'b', 'c'}  {'c', 'c', 'c', 'b', 'b', 'a'}

LIST/SEQUENCE¶

LIST (= SEQUENCE) is a collection type in which the input order of elements is preserved, and duplications are allowed. Elements of a LIST are allowed to have only one data type. It can have records of other tables.

CREATE TABLE list_tbl (col_1 LIST (CHAR(1)));
INSERT INTO list_tbl VALUES ({'c','c','c','b','b', 'a'});
SELECT * FROM list_tbl;

  col_1
======================
  {'c', 'c', 'c', 'b', 'b', 'a'}

SELECT CAST(col_1 AS SET), CAST(col_1 AS MULTISET) FROM list_tbl;

   cast(col_1 as set)  cast(col_1 as multiset)
============================================
  {'a', 'b', 'c'}  {'a', 'b', 'b', 'c', 'c', 'c'}

Conversion Rules¶

CREATE TABLE t(i INT);
INSERT INTO t VALUES('123');

SELECT * FROM t;

            i
=============
          123

SELECT MOD('123','2');

           mod('123', '2')
==========================
     1.000000000000000e+00